import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime

# 设置中文字体
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

try:
    # 读取Excel文件
    data = pd.read_excel('FhjlViewDD.xlsx')
    
    # 打印列名用于调试
    print("Excel文件列名:", data.columns.tolist())
    
    # 转换日期列并筛选6月数据
    data['创建时间'] = pd.to_datetime(data['创建时间'])
    june_data = data[data['创建时间'].dt.month == 6]
    
    # 按车辆分组汇总净重
    vehicle_freight = june_data.groupby('车辆')['净重'].sum().sort_values(ascending=False)
    
    # 保存到Excel
    vehicle_freight.to_excel('vehicle_freight_summary.xlsx')
    
    # 生成TOP20车辆货运量柱状图
    top20 = vehicle_freight.head(20)
    plt.figure(figsize=(12, 6))
    top20.plot(kind='bar')
    plt.title('6月份车辆货运量TOP20')
    plt.xlabel('车牌号')
    plt.ylabel('货运量(吨)')
    plt.tight_layout()
    plt.savefig('vehicle_freight_summary.png', dpi=300)
    plt.close()
    
    print("分析完成，结果已保存到vehicle_freight_summary.xlsx和vehicle_freight_summary.png")
    
except FileNotFoundError:
    print("错误：未找到FhjlViewDD.xlsx文件")
except Exception as e:
    print(f"发生错误：{str(e)}")